#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

import MySQLdb
from pymongo import MongoClient

# MySQL Info
DATABASE_NAME = 'pmc_meta'
HOST = '10.10.31.17'
PORT = '3306'
USER_NAME = 'pmc'
PASSWORD = 'pmc'
CHAR_SET = 'utf8'

# MongoDB Info
SERVER = 'localhost'
PORT = 27017
DB_NAME = "journal"
COLLECTION_NAME = "journal"

connection = MongoClient(SERVER, PORT)
db = connection[DB_NAME]
collection = db[COLLECTION_NAME]

conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)

cursor = conn.cursor()
sql = "SELECT id,issn_print,issn_electronic,title,isoabbreviation,Medline_TA,Unique_Nlm_ID FROM tb_dds_journal"
n = cursor.execute(sql)

issn_miss = 0
issn_unique = 0
issn_multi = 0

# title_miss = 0
# title_unique = 0
# title_multi = 0
# issnmiss = []
# issnmulti = []
# titlemiss = []
# titlemulti = []

for row in cursor.fetchall():
    pISSN = ""
    eISSN = ""
    FullTitle = ""
    ISOAbbr = ""
    MedAbbr = ""
    NlmId = ""

    id = row[0]
    if row[1] != None:
        pISSN = row[1]
    if row[2] != None:
        eISSN = row[2]
    if row[3] != None:
        FullTitle = row[3]
    if row[4] != None:
        ISOAbbr = row[4]
    if row[5] != None:
        MedAbbr = row[5]
    if row[6] != None:
        NlmId = row[6]

    if pISSN != "" or eISSN != "":
        results = collection.find({"$or": [{"ISSNSet": pISSN}, {"ISSNSet": eISSN}]}).count()
        if results == 0:
            issn_miss += 1
            # issnmiss.append(id)
        elif results == 1:
            issn_unique += 1
            param = []
            result = collection.find_one({"$or": [{"ISSNSet": pISSN}, {"ISSNSet": eISSN}]})
            sql = "UPDATE tb_dds_journal SET "
            add = ""
            if pISSN == "" and result.has_key("pISSN"):
                add += "issn_print=%s,"
                param.append(result["pISSN"])
            if eISSN == "" and result.has_key("eISSN"):
                add += "issn_electronic=%s,"
                param.append(result["eISSN"])
            if FullTitle == "" and result.has_key("FullTitle"):
                add += "title=%s,"
                param.append(result["FullTitle"][0])
            if ISOAbbr == "" and result.has_key("ISOAbbr"):
                add += "isoabbreviation=%s,"
                param.append(result["ISOAbbr"][0])
            if MedAbbr == "" and result.has_key("MedAbbr"):
                add += "Medline_TA=%s,"
                param.append(result["MedAbbr"][0])
            if NlmId == "" and result.has_key("NlmId"):
                add += "Unique_Nlm_ID=%s,"
                param.append(result["NlmId"])
            if add != "":
                sql += add[:-1] + " WHERE id=%s"
                param.append(str(id))
                print sql
                print param
                cursor2 = conn.cursor()
                result2 = cursor2.execute(sql, param)
                conn.commit()
                cursor2.close()

        elif results > 1:
            issn_multi += 1
            # issnmulti.append(id)
            # else:
            #     ors = []
            #     if FullTitle != "":
            #         ors.append({"FullTitle": {"$regex": "^" + FullTitle + "$", "$options": "$i"}})
            #     if ISOAbbr != "":
            #         ors.append({"ISOAbbr": {"$regex": "^" + ISOAbbr + "$", "$options": "$i"}})
            #         ors.append({"MedAbbr": {"$regex": "^" + ISOAbbr + "$", "$options": "$i"}})
            #         ors.append({"JCRAbbr": {"$regex": "^" + ISOAbbr + "$", "$options": "$i"}})
            #     if MedAbbr != "":
            #         ors.append({"ISOAbbr": {"$regex": "^" + MedAbbr + "$", "$options": "$i"}})
            #         ors.append({"MedAbbr": {"$regex": "^" + MedAbbr + "$", "$options": "$i"}})
            #         ors.append({"JCRAbbr": {"$regex": "^" + MedAbbr + "$", "$options": "$i"}})
            #     if len(ors) > 1:
            #         results = collection.find({"$or": ors}).count()
            #         if results == 0:
            #             title_miss += 1
            #             titlemiss.append(id)
            #         elif results == 1:
            #             title_unique += 1
            #         elif results > 1:
            #             title_multi += 1
            #             titlemulti.append(id)
            #     elif len(ors) == 1:
            #         results = collection.find(ors[0]).count()
            #         if results == 0:
            #             title_miss += 1
            #             titlemiss.append(id)
            #         elif results == 1:
            #             title_unique += 1
            #         elif results > 1:
            #             title_multi += 1
            #             titlemulti.append(id)
cursor.close()
conn.close()
connection.close()

print "ISSN MISS: " + str(issn_miss)
print "ISSN UNIQUE: " + str(issn_unique)
print "ISSN MULTI: " + str(issn_multi)
# print "TITLE MISS" + str(title_miss)
# print "TITLE UNIQUE" + str(title_unique)
# print "TITLE MULTI" + str(title_multi)
# print issnmiss
# print issnmulti
# print titlemiss
# print titlemulti
